package com.koron.tool.dialog;

import java.awt.Dimension;
import java.awt.Point;
import java.awt.event.ActionEvent;
import java.awt.event.ItemEvent;
import java.awt.event.ItemListener;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;
import java.util.ResourceBundle;

import javax.swing.*;
import javax.swing.event.ListSelectionEvent;
import javax.swing.event.ListSelectionListener;

import com.koron.tool.Constant;
import com.koron.tool.MainFrame;
import com.koron.ui.MutableGridLayout;

@SuppressWarnings("serial")
public class SQLAssistantDialog extends JDialog {
	private final ResourceBundle resourceBuddle = ResourceBundle.getBundle(MainFrame.RESOURCEBUDDLE, getLocale());
	/**
	 * 数据表展示的表格模型
	 */
	private DefaultListModel<String> dlmTable = new DefaultListModel<>();
	/**
	 * 字段列表的表格模型
	 */
	private DefaultListModel<String> dlmColumn = new DefaultListModel<>();
	/**
	 * 生成SQL语句时的风格，缺省为MYSQL的风格
	 */
	private char[] prefix = new char[] { '`', '`', '`', '`' };
	/**
	 * SQL语句的风格
	 */
	private final String[] sqlStyleArray = new String[] { resourceBuddle.getString("MYSQL"),
			resourceBuddle.getString("MSSQL"), resourceBuddle.getString("SQLITE"), resourceBuddle.getString("NOSTYLE") };
	/**
	 * 选择生成SQL语句的风格
	 */
	private JComboBox<String> cbxSqlStyle = new JComboBox<>(sqlStyleArray);
	/**
	 * 数据表展示表格
	 */
	private JList<String> lstTable = new JList<>(dlmTable);
	/**
	 * 字段列表的表格
	 */
	private JList<String> lstColumn = new JList<>(dlmColumn);
	/**
	 * 用来展示生成SQL语句的输入框
	 */
	private JTextArea jta = new JTextArea();
	/**
	 * 数据库链接用到的属性
	 */
	private Properties p;

	public SQLAssistantDialog(final JFrame frame) {
		super(frame, "", false);
		setTitle(resourceBuddle.getString("SQLAssistant"));

		MutableGridLayout layout = new MutableGridLayout(3, 4, 5, 5);

		layout.merge(1, 0, 2, 1);
		layout.merge(0, 3, 2, 3);

		layout.setHeight(0, 20);
		layout.setHeight(1, 0.5);
		layout.setHeight(2, 20);
		layout.setHeight(3, 0.5);
		getContentPane().setLayout(layout);
		cbxSqlStyle.addItemListener(new ItemListener() {
			@Override
			public void itemStateChanged(ItemEvent e) {
				if (e.getStateChange() == ItemEvent.SELECTED) {
					for (int i = 0; i < sqlStyleArray.length; i++) {
						if (e.getItem().toString().equals(sqlStyleArray[i])) {
							switch (i) {
							case 0:
								prefix = new char[] { '`', '`', '`', '`' };
								break;
							case 1:
								prefix = new char[] { '[', ']', '[', ']' };
								break;
							case 2:
								prefix = new char[] { '"', '"', '"', '"' };
								break;
							case 3:
								prefix = new char[] { ' ', ' ', ' ', ' ' };
								break;
							}
							break;
						}
					}
				}
			}
		});
		add(cbxSqlStyle);

		JScrollPane jsp = new JScrollPane(lstColumn);
		jsp.setPreferredSize(new Dimension(20, 20));
		getContentPane().add(jsp);

		jsp = new JScrollPane(lstTable);
		lstTable.addListSelectionListener(new ListSelectionListener() {
			@Override
			public void valueChanged(ListSelectionEvent e) {
				List<String> values = lstTable.getSelectedValuesList();
				dlmColumn.clear();
				try {
					Connection conn = getConnection();
					for (String string : values) {
						ResultSet rs = conn.getMetaData().getColumns(null, null, string, null);
						while (rs.next()) {
							dlmColumn.addElement(rs.getString("COLUMN_NAME") + "(" + rs.getString("REMARKS") + ")");
						}
						rs.close();
					}
					conn.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
		});
		jsp.setPreferredSize(new Dimension(20, 20));
		getContentPane().add(jsp);

		getContentPane().add(new JButton(new InsertAction()));
		getContentPane().add(new JButton(new UpdateAction()));
		getContentPane().add(new JButton(new SelectAction()));
		jsp = new JScrollPane(jta);
		jsp.setPreferredSize(new Dimension(20, 20));
		getContentPane().add(jsp);

		setSize(800, 600);
		Point p = frame.getLocationOnScreen();
		p.x = p.x + (frame.getWidth() - getWidth()) / 2;
		p.y = p.y + (frame.getHeight() - getHeight()) / 2;
		setLocation(p);
		init();

		setVisible(true);
	}

	public void init() {
		try {
			Connection conn = getConnection();
			ResultSet rs = conn.getMetaData().getTables(null, null, "", new String[] { "VIEW", "TABLE" });
			while (rs.next()) {
				dlmTable.addElement(rs.getString("TABLE_NAME"));
			}
			rs.close();
			conn.close();
		} catch (SQLException | NullPointerException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 获取数据库链接
	 * 
	 * @return
	 */
	private Connection getConnection() {
		try {
			return MainFrame.getConnection(Constant.DEVELOPDB);
		} catch (ClassNotFoundException | NullPointerException | SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 生成UPDATE语句的按钮对应的事件
	 * 
	 * @author 方志文
	 * 
	 */
	public class UpdateAction extends AbstractAction {
		UpdateAction() {
			this.putValue(NAME, resourceBuddle.getString("GenerateUpdate"));
			this.putValue(MNEMONIC_KEY, (int) 'u');
		}

		@Override
		public void actionPerformed(ActionEvent e) {
			StringBuilder sb = new StringBuilder();
			sb.append("update ").append(prefix[0]).append(lstTable.getSelectedValue()).append(prefix[1])
					.append(" set \n");
			for (String column : lstColumn.getSelectedValuesList()) {
				if (column.indexOf('(') == -1) {
					sb.append(prefix[2]).append(column).append(prefix[3]).append("=#{").append(column).append("},\n");
				} else {
					sb.append(prefix[2]).append(column.substring(0, column.indexOf('('))).append(prefix[3])
							.append("=#{").append(column.substring(0, column.indexOf('('))).append("},\n");
				}
			}
			if (sb.charAt(sb.length() - 2) == ',') {
				sb.deleteCharAt(sb.length() - 2);
			}
			jta.setText(sb.toString());
		}
	}

	/**
	 * 用来生成INSERT语句的按钮的事件
	 * 
	 * @author 方志文
	 * 
	 */
	public class InsertAction extends AbstractAction {
		InsertAction() {
			this.putValue(NAME, resourceBuddle.getString("GenerateInsert"));
			this.putValue(MNEMONIC_KEY, (int) 'i');
		}

		@Override
		public void actionPerformed(ActionEvent e) {
			StringBuilder sb = new StringBuilder();
			StringBuilder sb2 = new StringBuilder();
			sb.append("insert into ").append(prefix[0]).append(lstTable.getSelectedValue()).append(prefix[1])
					.append("\n");
			sb.append("(");
			for (String column : lstColumn.getSelectedValuesList()) {
				if (column.indexOf('(') == -1) {
					sb.append(prefix[2]).append(column).append(prefix[3]).append(",");
					sb2.append("#{").append(column).append("},");
				} else {
					sb.append(prefix[2]).append(column.substring(0, column.indexOf('('))).append(prefix[3]).append(",");
					sb2.append("#{").append(column.substring(0, column.indexOf('('))).append("},");
				}
			}
			if (sb.charAt(sb.length() - 1) == ',') {
				sb.deleteCharAt(sb.length() - 1);
				sb2.deleteCharAt(sb2.length() - 1);
			}
			sb.append(") values \n(").append(sb2.toString()).append(")");
			jta.setText(sb.toString());
		}
	}

	/**
	 * 生成SELECT语句的按钮事件
	 * 
	 * @author 方志文
	 * 
	 */
	public class SelectAction extends AbstractAction {
		SelectAction() {
			this.putValue(NAME, resourceBuddle.getString("GenerateSelect"));
			this.putValue(MNEMONIC_KEY, (int) 's');
		}

		@Override
		public void actionPerformed(ActionEvent e) {
			StringBuilder sb = new StringBuilder();
			sb.append("select ");
			for (String column : lstColumn.getSelectedValuesList()) {
				if (column.indexOf('(') == -1)
					sb.append(prefix[0]).append(column).append(prefix[1]).append(",");
				else
					sb.append(prefix[2]).append(column.substring(0, column.indexOf('('))).append(prefix[3]).append(",");
			}
			if (sb.charAt(sb.length() - 1) == ',') {
				sb.deleteCharAt(sb.length() - 1);
			}
			sb.append(" from ").append(prefix[0]).append(lstTable.getSelectedValue()).append(prefix[1]).append("\n");
			jta.setText(sb.toString());
		}
	}
}